package com.demo.openlktest.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Component
public class CheckDao {
    @Autowired
    JdbcTemplate jdbcTemplate;

    public int checkCreditRecords(String personID){
        String sql = "select count(*) from mongodb_credit.credit.credit_records as cr inner join mysql_credit.credit.person_info as pi " +
                "on pi.id = cr.person_id where pi.id_num = ?";
        List<Integer> numList = new ArrayList<>();
        jdbcTemplate.query(sql, resultSet -> {
            numList.add(resultSet.getInt(1));
        }, personID);
        return numList.get(0);
    }

    public void test() {
        String sql = "show catalogs";
        jdbcTemplate.query(sql, resultSet -> {
            System.out.println(resultSet.getString(1));
        });
    }

    public float checkIncomeForOneYearByOneCard(String personID, String cardNum) {
        String sql = "select sum(ti.price) as total_income from hive_bank.bank.transaction_info as ti inner join mysql_bank.bank.person_info as pi " +
                "on pi.id = cr.person_id where pi.id_num=? and (ti.type = '收入' or ti.dest_card_num = ?) and ti.time between now() - interval '1' year and now()";
        List<Integer> numList = new ArrayList<>();
        jdbcTemplate.query(sql, resultSet -> {
            numList.add(resultSet.getInt(1));
        }, personID, cardNum);
        return numList.get(0);
    }

    public float checkOutcomeForOneYearByOneCard(String personID, String cardNum) {
        String sql = "select sum(ti.price) as total_outcome from hive_bank.bank.transaction_info as ti inner join mysql_bank.bank.person_info as pi" +
                "on pi.id = cr.person_id where pi.id_num=? and (ti.type = '支出' or ti.src_card_num = ?) and ti.time between now() - interval '1' year and now()";
        List<Integer> numList = new ArrayList<>();
        jdbcTemplate.query(sql, resultSet -> {
            numList.add(resultSet.getInt(1));
        }, personID, cardNum);
        return numList.get(0);
    }

    public List<String> selectAllCardNumByPersonID(String personID) {
        String sql = "select dc.card_num, cc.card_num from mysql_bank.bank.debit_card as dc " +
                "inner join mysql_bank.bank.person_info as pi on dc.person_id = pi.id " +
                "inner join mysql_bank.bank.credit_card as cc on cc.person_id = pi.id" +
                "where pi.id_num=?";
        List<String> cards = new ArrayList<>();
        jdbcTemplate.query(sql, resultSet -> {
            cards.add(resultSet.getString(1));
            cards.add(resultSet.getString(2));
        }, personID);
        return cards;
    }
}
